package newRegFrm.dao;

import newRegFrm.model.User;
import user.ToolUtil;
import user.c3p0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {
    //登录验证
    public User login1(String username, String password)throws SQLException {
        String sql = "select * from user WHERE username = ? and password = ?";
        try (Connection conn = c3p0Util.getConnection()) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setRole(rs.getInt("role"));
                    return user;
                }
            }
        }
            return null;     //登录失败
    }

//    public User register1(String username, String password) throws SQLException {
//        String sql = "SELECT * FROM user WHERE username = ?";
//        PreparedStatement pstmt = (PreparedStatement) conn
//    }
    public User login(Connection conn, User user) throws Exception {
        User resultUser = null;
        String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        pstmt.setInt(3, user.getRole());
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            resultUser = new User();
            resultUser.setUsername(rs.getString("username"));
        }
        return resultUser;
    }
    public static int addUser(Connection conn, User user) throws Exception {
        // 检测注册用户是否存在
        String sql = "SELECT * FROM user where username = ? and password =?";
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            return 2;
        }
        pstmt.close();
        rs.close();

        sql = "INSERT INTO user (username, password, role) VALUES (?, ?, ?)";
        PreparedStatement pstmt2 = (PreparedStatement) conn.prepareStatement(sql);
        pstmt2.setString(1, user.getUsername());
        pstmt2.setString(2, user.getPassword());
        pstmt2.setInt(3, user.getRole());
        return pstmt2.executeUpdate();
    }

    public ResultSet list(Connection conn, User user) throws Exception {
        StringBuffer sb = new StringBuffer("select * from user where role = 1");
        if (!ToolUtil.isEmpty(user.getUsername())) {
            sb.append(" and username like '%"+user.getUsername()+"%'");
        }
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sb.toString());
        return pstmt.executeQuery();
    }

    public int update(Connection conn, User user) throws Exception {
        String sql = "UPDATE user SET password = ? WHERE username = ?";
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        return pstmt.executeUpdate();
    }
}
